package com.hehao.java_basic.doc_poi.service.impl;

import cn.hutool.core.convert.impl.StringConverter;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson2.JSON;
import com.hehao.java_basic.doc_poi.demoEntity.DemoImportListener;
import com.hehao.java_basic.doc_poi.demoEntity.EasyExcelDemoData;
import com.hehao.common.entiy.User;
import com.hehao.java_basic.doc_poi.service.PoiService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.util.Collections;
import java.util.List;

/**
 * @author Damon
 * @date 2022/8/2
 */
@Service
@Slf4j
public class PoiServiceImpl implements PoiService {
    private static final int POSITION_ROW = 1;
    private static final int POSITION_COL = 0;

    /**
     * apache poi方式导出
     *
     * @return
     */
    @Override
    public SXSSFWorkbook exportExcel01() {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet();

        int rows = POSITION_ROW;
        int cols = POSITION_COL;
        String[] columns = new String[]{"ID", "Name", "Email", "Phone", "Description"};
        int[] colWidths = new int[]{2000, 3000, 5000, 5000, 8000};

        SXSSFRow headRow = sheet.createRow(0);
        CellStyle headRowStyle = getHeadCellStyle(workbook);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.length - 1));
        SXSSFCell cell = headRow.createCell(0);
        cell.setCellStyle(headRowStyle);
        cell.setCellValue("合并单元格");
        // 1.表头
        Row head = sheet.createRow(rows++);
        CellStyle headStyle = getHeadCellStyle(workbook);
        for (int i = 0; i < columns.length; i++) {
            sheet.setColumnWidth(cols, colWidths[i]);
            addCellWithStyle(head, cols++, headStyle).setCellValue(columns[i]);
        }
        // 2.内容
        CellStyle bodyStyle = getBodyCellStyle(workbook);
        List<User> userList = getUserList();
        for (User user : userList) {
            int tempCols = POSITION_COL;
            SXSSFRow row = sheet.createRow(rows++);
            addCellWithStyle(row, tempCols++, bodyStyle).setCellValue(user.getId());
            addCellWithStyle(row, tempCols++, bodyStyle).setCellValue(user.getUserName());
            addCellWithStyle(row, tempCols++, bodyStyle).setCellValue(user.getEmail());
            addCellWithStyle(row, tempCols++, bodyStyle).setCellValue(String.valueOf(user.getPhoneNumber()));
            addCellWithStyle(row, tempCols, bodyStyle).setCellValue(user.getDescription());
        }
        return workbook;
    }

    /**
     * apache poi方式导入
     *
     * @return
     */
    @Override
    public void importExcel01(InputStream inputStream) throws IOException {
        XSSFWorkbook book = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = book.getSheetAt(0);
        //解析数据
        int cols;
        for (int i = POSITION_ROW; i < sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i + 1);
            cols = POSITION_COL;
            User user = User.builder()
                    .id(getCellLongValue(row.getCell(cols++)))
                    .userName(getCellStringValue(row.getCell(cols++)))
                    .email(getCellStringValue(row.getCell(cols++)))
                    .phoneNumber(Long.parseLong(getCellStringValue(row.getCell(cols++))))
                    .description(getCellStringValue(row.getCell(cols)))
                    .build();
            log.info(JSON.toJSONString(user));
        }
        book.close();
    }


    /**
     * poi 组装样式
     *
     * @param row
     * @param colPosition
     * @param cellStyle
     * @return
     */
    private Cell addCellWithStyle(Row row, int colPosition, CellStyle cellStyle) {
        Cell cell = row.createCell(colPosition);
        cell.setCellStyle(cellStyle);
        return cell;
    }

    /**
     * 表头样式
     *
     * @param workbook
     * @return
     */
    private CellStyle getHeadCellStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);

        // fill
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return style;
    }

    /**
     * 通用样式设置
     *
     * @param workbook
     * @return
     */
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // font
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);

        // align
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.TOP);

        // border
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        return style;
    }

    /**
     * 表格内容样式设置
     *
     * @param workbook
     * @return
     */
    private CellStyle getBodyCellStyle(Workbook workbook) {
        return getBaseCellStyle(workbook);
    }

    /**
     * mock user
     *
     * @return
     */
    private List<User> getUserList() {
        return Collections.singletonList(User.builder()
                .id(1L).userName("pdai").email("pdai@pdai.tech").phoneNumber(121231231231L)
                .description("hello world")
                .build());
    }

    /**
     * 获取单元格String值
     *
     * @param cell
     * @return
     */
    private String getCellStringValue(XSSFCell cell) {
        try {
            if (null != cell) {
                return String.valueOf(cell.getStringCellValue());
            }
        } catch (Exception e) {
            return String.valueOf(getCellIntValue(cell));
        }
        return "";
    }

    /**
     * 获取单元格Long值
     *
     * @param cell
     * @return
     */
    private long getCellLongValue(XSSFCell cell) {
        try {
            if (null != cell) {
                return Long.parseLong("" + (long) cell.getNumericCellValue());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0L;
    }

    /**
     * 获取单元格int值
     *
     * @param cell
     * @return
     */
    private int getCellIntValue(XSSFCell cell) {
        try {
            if (null != cell) {
                return Integer.parseInt("" + (int) cell.getNumericCellValue());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }


    /**
     * easy excel方式导入
     *
     * @param inputStream
     */
    @Override
    public void importEasyExcel(InputStream inputStream) {
        List<EasyExcelDemoData> list = EasyExcel.read(inputStream)
                .head(EasyExcelDemoData.class)
                // 设置sheet,默认读取第一个
                .sheet()
                // 设置标题所在行数
                .headRowNumber(2)
                // 这个转换是成全局的， 所有java为string,excel为string的都会用这个转换器。
                // 如果就想单个字段使用请使用@ExcelProperty 指定converter
                //.registerConverter(new StringConverter())
                .registerReadListener(new DemoImportListener())
                .doReadSync();
        System.out.println(JSON.toJSONString(list));
    }

}
